#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Datetime: 2020/11/17 下午1:39
# @Author  : HUANG Xiong
# @Site    : 
# @File    : api_func_old.py
# @Software: PyCharm 

"""
脚本说明：之前强哥写的一些取数据的函数，目前在datasource_fetch中已写了新函数，但是之前用到这些函数的脚本还没改
"""
import pandas as pd

import TQR_Applications.test_me.test
import quant_researcher.quant.project_tool.common_var
from quant_researcher.quant.datasource_fetch.fund_api import fund_holding_related
from quant_researcher.quant.project_tool import hammer, time_tool
from quant_researcher.quant.project_tool.db_operator import db_conn, my_mysql
from quant_researcher.quant.project_tool.logger.my_logger import LOG
from quant_researcher.quant.project_tool.wrapper_tools import common_wrappers, conn_wrappers
from quant_researcher.quant.project_tool.wrapper_tools.common_wrappers import automatic_date_formatter_wrapper

C_C = 'fund_code'
M_C = 'manager_code'
C_BOND_C = 'bond_code'
C_R_TYPE = 'rept_type'
C_NET_ASSET = 'net_asset'  # 基金净资产
C_R_E_DATE = 'rept_enddate'
C_PCT_OF_NV = 'pptinnv'
T_FUND_REPORT_ASSET_ALLOCATION = 'mf_iv_fndassetportfolio'
T_FUND_REPORT_BOND_ALLOCATION = 'mf_iv_bondportfolio'
TABLE_SPECIFICS = {
    C_R_E_DATE: {'fmt': quant_researcher.quant.project_tool.common_var.PY_DATE_FMT_WITHOUT_N_DASH, 'class': 'str'}
}
T_FUND_REPORT_INDU_ALLOCATION = 'mf_iv_stkindualloc'
C_CSRC = 'indu_code'
T_FUND_REPORT_STOCK_ALLOCATION = 'mf_iv_stockportfolio'
C_STK_C = 'stock_code'
T_FUND_INFO = 'mf_bd_fndinfo'
C_M_S = 'manage_start_date'
C_M_E = 'manage_end_date'
C_F_T_1 = 'fund_type_1'
C_F_T_2 = 'fund_type_2'
C_E_D = 'end_date'
C_TOTAL_ASSET = 'total_assets'
T_MGR_FUND_STAT = 'mf_di_mgr_fndstats'
MGR_FUND_DF = None


def get_stock_portfolio_report_by_fund_code(x=None, end_date=None, r_type=None, **kwargs):
    """
    查询基金的股票持仓数据

    :param str x: 基金代码，默认：None
    :param str end_date: 报告日期，默认：None
    :param str r_type: 报告类型，默认：None
    :param kwargs:
        - conn，obj，连接对象，默认：None
        - select，list，选择哪些列，默认：None（按照配置读取）
        - order_by，str，按照什么排序，默认：None（无）
        - extra_filter，str，额外的筛选条件，默认：None（无）
    :return: pd.DataFrame，查询结果
    """
    conn = kwargs.pop('conn', None)
    select = kwargs.pop('select', None)
    order_by = kwargs.pop('order_by', None)
    extra_filter = kwargs.pop('extra_filter', None)

    conn = db_conn.get_basic_data_conn() if conn is None else conn
    if select is None:
        select = [C_C, C_STK_C, C_PCT_OF_NV]
    where = []
    if x is not None:
        where.append(f'{C_C}=\'{x}\'')
    if end_date is not None:
        tmp_end_date = end_date.replace('-', '')
        where.append(f'{C_R_E_DATE}=\'{tmp_end_date}\'')
    if r_type is not None:
        where.append(f'{C_R_TYPE}=\'{r_type}\'')
    if extra_filter is not None:
        where.append(extra_filter)
    df = my_mysql.read_v2(
        select=select, where=where, sfrom=T_FUND_REPORT_STOCK_ALLOCATION,
        order_by=order_by, conn=conn, **kwargs
    )
    df.dropna(inplace=True)
    hammer.slim(df, name=f'报告：end_date={end_date} r_type={r_type}', a_f=1,
                show_index=1)

    return df


def get_distinct_date_and_type_in_stock_portfolio_report(**kwargs):
    return TQR_Applications.test_me.test.distinct_some_cols(
        cols=[C_R_E_DATE, C_R_TYPE],
        date_format_this_col=C_R_E_DATE,
        table=T_FUND_REPORT_STOCK_ALLOCATION, **kwargs
    )


def get_all_date_and_type_in_stock_portfolio_report():
    return get_distinct_date_and_type_in_stock_portfolio_report(
        other_filter=f'{C_R_TYPE} in (1, 2, 3, 4, 5, 6)'
    )


def get_stock_indu_alloc_report(fund_code=None, end_date=None, r_type=None, **kwargs):
    """
    查询基金的股票持仓数据
    :param fund_code str，基金代码，默认：None
    :param end_date str，报告日期，默认：None
    :param r_type str，报告类型，默认：None
    :param kwargs 关键字参数
        conn，obj，连接对象，默认：None
        select，list，选择哪些列，默认：None（按照配置读取）
        order_by，str，按照什么排序，默认：None（无）
        extra_filter，str，额外的筛选条件，默认：None（无）
    :return
    """
    conn = kwargs.pop('conn', None)
    select = kwargs.pop('select', None)
    order_by = kwargs.pop('order_by', None)
    extra_filter = kwargs.pop('extra_filter', None)

    conn = db_conn.get_global_basic_data_conn() if conn is None else conn
    if select is None:
        select = [C_C, C_CSRC, C_PCT_OF_NV]
    where = []
    if fund_code is not None:
        where.append(f'{C_C}=\'{fund_code}\'')
    if end_date is not None:
        tmp_end_date = end_date.replace('-', '')
        where.append(f'{C_R_E_DATE}=\'{tmp_end_date}\'')
    if r_type is not None:
        where.append(f'{C_R_TYPE}=\'{r_type}\'')
    if extra_filter is not None:
        where.append(extra_filter)
    df = my_mysql.read_v2(
        select=select, where=where, sfrom=T_FUND_REPORT_INDU_ALLOCATION,
        order_by=order_by, conn=conn, **kwargs
    )
    df.dropna(inplace=True)
    hammer.slim(df, a_f=1, show_index=1,
                name=f'行业持仓报告：end_date={end_date} r_type={r_type}')

    return df


def check_report(df):
    for a_record in df[[C_C, C_R_TYPE, 'tj', C_NET_ASSET]].values:
        u_code, r_type, a_date, a_value = a_record
        if a_value is None or a_date is None:
            err_msg = f'基金 {u_code}，报告（类型：{r_type}）数据存在空值！' \
                      f'日期={a_date}，净资产（{C_NET_ASSET}）={a_value}'
            LOG.error(err_msg)
            raise RuntimeError(err_msg)


@common_wrappers.rename_date_col_to_tj(C_R_E_DATE)
@common_wrappers.automatic_date_formatter_wrapper(TABLE_SPECIFICS)
@conn_wrappers.conn_meddler(conn_getter=db_conn.get_basic_data_conn)
def get_latest_asset_portfolio_report_by_fund(x, *, conn, deadline=None, **kwargs):
    cols_needed = kwargs.pop('cols_needed', None)
    if cols_needed is None:
        select = '*'
    else:
        select = [C_C, C_R_E_DATE, C_R_TYPE]
        select.extend(cols_needed)

    where = [f'{C_C}=\'{x}\'', f'rept_type in (1, 2, 3, 4)']

    if deadline is not None:
        where.append(f'{C_R_E_DATE}<=\'{deadline.replace("-", "")}\'')

    df = my_mysql.read_v2(
        select=select,
        sfrom=T_FUND_REPORT_ASSET_ALLOCATION,
        where=where,
        order_by=f'{C_R_E_DATE} desc', limit=1, conn=conn
    )
    hammer.slim(df, name=f'查到的基金 {x} report 数据', a_f=1)
    return df


def get_distinct_type_and_date_in_asset_portfolio_report(**kwargs):
    select = f'distinct {C_R_TYPE}, {C_R_E_DATE}'
    where = kwargs.pop('where', None)
    order_by = kwargs.pop('order_by', None)
    date_fmt_this_col = kwargs.pop('date_fmt_this_col', None)

    df = my_mysql.read_v2(
        select=select, sfrom=T_FUND_REPORT_ASSET_ALLOCATION, conn=db_conn.get_global_basic_data_conn(),
        where=where, order_by=order_by
    )

    if date_fmt_this_col is not None:
        df[date_fmt_this_col] = df[date_fmt_this_col].apply(
            time_tool.format_date_str
        )

    ans = list(df[[C_R_TYPE, C_R_E_DATE]].values)
    return ans


def get_asset_portfolio_report_detail_by_fund_code(x=None, need_season_report=True, **kwargs):
    conn = kwargs.pop('conn', db_conn.get_global_basic_data_conn())
    select = kwargs.pop('select', '*')
    end_date = kwargs.pop('end_date', None)
    extra_filter = kwargs.pop('extra_filter', None)
    report_type = kwargs.pop('report_type', None)

    where = []
    if x is not None:
        if isinstance(x, str):
            x = [x]
        x = [f'\'{a_code}\'' for a_code in x]
        x = ', '.join(x)
        x = f'({x})'
        where.append(f'{C_C} in {x}')
    if end_date is not None:
        where.append(f'{C_R_E_DATE}=\'{end_date}\'')
    if report_type is None:
        if need_season_report:
            report_type = '(1, 2, 3, 4)'  # 只选择一，二，三，四季报数据
        else:
            report_type = '(5, 6)'  # 半年报，年报
        where.append(f'{C_R_TYPE} in {report_type}')
    else:
        where.append(f'{C_R_TYPE}={report_type}')

    if extra_filter is not None:
        where.append(extra_filter)

    order_by = None
    # order_by = C_R_E_DATE
    df = my_mysql.read_v2(
        select=select, where=where, sfrom=T_FUND_REPORT_ASSET_ALLOCATION, order_by=order_by,
        conn=conn
    )
    return df


def get_asset_portfolio_report_by_fund_code(x, need_season_report=True, **kwargs):
    conn = kwargs.pop('conn', None)
    select = [C_R_E_DATE, C_NET_ASSET]
    where = f'{C_C}=\'{x}\''
    if need_season_report:
        report_type = '(1, 2, 3, 4)'  # 只选择一，二，三，四季报数据
    else:
        report_type = '(5, 6)'  # 半年报，年报
    where += f' and {C_R_TYPE} in {report_type}'
    order_by = C_R_E_DATE
    df = my_mysql.read_v2(
        select=select, where=where, sfrom=T_FUND_REPORT_ASSET_ALLOCATION, order_by=order_by,
        conn=conn
    )
    return df


def get_distinct_date_and_type_in_bond_portfolio_report(**kwargs):
    return TQR_Applications.test_me.test.distinct_some_cols(
        cols=[C_R_E_DATE, C_R_TYPE],
        date_format_this_col=C_R_E_DATE,
        table=T_FUND_REPORT_BOND_ALLOCATION, **kwargs
    )


def get_bond_holding_by_fund(x, end_date, r_type, how_many, **kwargs):
    a_col = C_PCT_OF_NV
    date_related_filter = [(C_R_E_DATE, end_date, end_date)]
    df = get_bond_portfolio_report_by_fund_code(
        x=x, date_related_filter=date_related_filter, r_type=r_type, **kwargs
    )
    df.dropna(inplace=True)
    df[a_col] = df[a_col] / 100
    hammer.slim(df, name='基金的债券持仓', a_f=1, show_index=1)
    if df.shape[0] != 0:
        df['group_sort'] = df[a_col].groupby(by=df[C_C]).rank(
            ascending=0, method='dense'
        )
        hammer.slim(df, name='经过排序之后', a_f=1)
        df = df[df['group_sort'] < (how_many + 1)]  # 这里要 +1
        hammer.slim(df, name='经过筛选之后', a_f=1)
        df.index = range(df.shape[0])
        df = df[[a_col, C_C]]
        df = df.groupby(by=C_C, as_index=False).sum()
    else:
        df = df[[a_col, C_C]]
    df.rename(columns={a_col: f'pct_of_first_{how_many}'}, inplace=True)
    hammer.slim(df, name=f'基金的债券持仓前 {how_many} 之和', a_f=1)
    return df


@common_wrappers.rename_date_col_to_tj(C_R_E_DATE)
@common_wrappers.automatic_date_formatter_wrapper(TABLE_SPECIFICS)
def get_bond_portfolio_report_by_fund_code(x=None, date_related_filter=None, **kwargs):
    """
    根据传入的筛选条件返回合适的基金报告数据
    :param x str，基金代码，默认：None（无基金代码条件）
    :param date_related_filter list of tuple，日期筛选，默认：None（无日期条件）
    :param kwargs 关键字参数
        r_type，int，报告类型，默认：None（无类型条件）
        conn，连接obj，默认：配置好的连接
        order_by，str，排序字段，默认：None（无排序）
        extra_filter，str，额外的筛选条件，默认：None（无其他条件）
    :return pd.DataFrame
    """
    r_type = kwargs.pop('r_type', None)
    conn = kwargs.pop('conn', None)
    order_by = kwargs.pop('order_by', None)
    extra_filter = kwargs.pop('extra_filter', None)

    conn = db_conn.get_global_basic_data_conn() if conn is None else conn
    select = [C_C, C_R_E_DATE, C_BOND_C, C_PCT_OF_NV]
    where = []

    if x is not None:
        where.append(f'{C_C}=\'{x}\'')
    if date_related_filter is not None:
        where.append(date_related_filter)
    if r_type is not None:
        where.append(f'{C_R_TYPE}=\'{r_type}\'')
    if extra_filter is not None:
        where.append(extra_filter)
    df = my_mysql.read_v2(
        select=select, where=where, sfrom=T_FUND_REPORT_BOND_ALLOCATION,
        order_by=order_by, conn=conn, **kwargs
    )
    return df


def get_distinct_date_and_type_in_stock_indu_alloc_report(**kwargs):
    return TQR_Applications.test_me.test.distinct_some_cols(
        cols=[C_R_E_DATE, C_R_TYPE],
        date_format_this_col=C_R_E_DATE,
        table=T_FUND_REPORT_INDU_ALLOCATION, **kwargs
    )


def get_asset_portfolio_report(fund_code=None, **kwargs):
    """
    查询基金报告数据
    :param fund_code str，基金代码，默认：None（无此筛选）
    :param kwargs 关键字参数
        conn，obj，连接对象，默认：None（从配置去读）
        report_type，str，报告类型
        end_date，str，报告日期
        select，list，需要获得哪些列，默认：None（返回配置的列）
        extra_filter，str，额外的筛选条件，默认：None
    :return pd.DataFrame
    """
    report_type = kwargs.pop('report_type')
    end_date = kwargs.pop('end_date')
    conn = kwargs.pop('conn', None)
    select = kwargs.pop('select', None)
    extra_filter = kwargs.pop('extra_filter', None)

    if conn is None:
        conn = db_conn.get_basic_data_conn()
    if select is None:
        select = [C_R_E_DATE, C_NET_ASSET]
    where = []
    if fund_code is not None:
        where.append(f'{C_C}=\'{fund_code}\'')
    where.append(f'{C_R_TYPE}={report_type}')
    tmp_end_date = end_date.replace('-', '')
    where.append(f'{C_R_E_DATE}=\'{tmp_end_date}\'')
    if extra_filter is not None:
        where.append(extra_filter)

    df = my_mysql.read_v2(
        select=select, where=where, sfrom=T_FUND_REPORT_ASSET_ALLOCATION, conn=conn, **kwargs
    )
    hammer.slim(
        df, a_f=1,
        name=f'基金={fund_code}，报告类型={report_type}，日期={end_date}的数据'
    )
    return df


def find_codes(where=None, conn=None, **kwargs):
    """
    后面逐渐淘汰

    :param where:
    :param conn:
    :param kwargs:
    :return:
    """
    select = kwargs.pop('select', C_C)
    close_conn = False
    if conn is None:
        conn = db_conn.get_basic_data_conn()
        close_conn = True
    try:
        df = my_mysql.read_v2(
            select=select, where=where, sfrom=T_FUND_INFO, conn=conn
        )
    finally:
        if close_conn:
            conn.close()
    hammer.slim(df, a_f=1, name=f'from {T_FUND_INFO} where {where}')
    return df


@conn_wrappers.conn_meddler(conn_getter=db_conn.get_derivative_data_conn)
def get_mgr_fund_weight(a_date, **kwargs):
    """
    查询基金经理管理的各类型基金中各基金权重（总资产）

    :param a_date: str，报告日期
    :param kwargs:
        - conn，obj，数据库连接
        - extra_filter，str，额外的筛选条件，默认：None
    :return: pd.DataFrame
    """
    conn = kwargs.pop('conn')
    extra_filter = kwargs.pop('extra_filter', None)

    # 基金代码 + 总资产
    # fund_asset_df1 = fund_holding_related.get_holding_report_info(
    #     select=['fund_code', 'total_assets'], end_date=a_date)
    fund_asset_df = fund_holding_related.get_holding_report_info(end_date=a_date, select=['fund_code', 'total_assets'],
                                                                 report_type=[1, 2, 3, 4])
    # 基金经理任职信息
    mgr_df = get_mgr_fund_stat(conn=conn, extra_filter=extra_filter)
    if mgr_df.shape[0] == 0:
        return
    del mgr_df[C_F_T_2]

    # 此日期前开始管理，此日期还未结束管理
    mgr_df = mgr_df[mgr_df[C_M_S] <= a_date]
    mgr_df[C_M_E] = mgr_df[C_M_E].fillna('--')
    mgr_df = mgr_df[(mgr_df[C_M_E] > a_date) | (mgr_df[C_M_E] == '--')]
    hammer.slim(mgr_df, show_index=1, a_f=1, name='按管理开始和结束筛选后的数据')
    del mgr_df[C_M_E]
    del mgr_df[C_M_S]

    df = mgr_df.merge(fund_asset_df, how='inner', on=C_C)
    hammer.slim(df, show_index=1, a_f=1, name='inner merge 基金资产后的数据')
    if df.shape[0] == 0:
        return

    # 增加 00 类型的基金
    tmp_df = df.copy()
    tmp_df[C_F_T_1] = '00'
    df = df.append(tmp_df)
    hammer.slim(df, show_index=1, a_f=1, name='增加 00 类型基金后的数据')

    # 按类型求总资产之和
    tmp_df = df[[M_C, C_F_T_1, C_TOTAL_ASSET]].groupby(
        by=[M_C, C_F_T_1], as_index=False
    ).sum()
    tmp_df.rename(columns={C_TOTAL_ASSET: 'a_type'}, inplace=True)
    hammer.slim(tmp_df, show_index=1, a_f=1, name='各类型基金资产总和数据')
    df = df.merge(tmp_df, how='left', on=[M_C, C_F_T_1])

    # 求每支基金在每类基金中的权重
    df['pct'] = df[C_TOTAL_ASSET] / df['a_type']
    hammer.slim(df, show_index=1, a_f=1, name='各基金在各类型基金中的权重数据')
    del df['a_type']  # 删除某类型基金总资产
    del df[C_TOTAL_ASSET]  # 删除某基金总资产
    df.rename(columns={C_F_T_1: 'fund_type'}, inplace=True)

    return df


@conn_wrappers.conn_meddler(conn_getter=db_conn.get_derivative_data_conn)
def get_mgr_fund_stat(a_date=None, **kwargs):
    conn = kwargs.pop('conn')
    extra_filter = kwargs.pop('extra_filter', None)

    if a_date is None:
        tmp_df = my_mysql.read_v2(
            select=f'max(end_date) as max_date', sfrom='mf_di_mgr_fndstats', conn=conn,
        )
        tmp_date = tmp_df['max_date'].values[0]
        a_date = tmp_date
    where = [f'{C_E_D}=\'{a_date}\'']
    if extra_filter is not None:
        where.append(extra_filter)

    df = my_mysql.read_v2(
        select=f'{M_C}, fund_code, {C_M_S}, {C_M_E}, {C_F_T_1}, {C_F_T_2}',
        sfrom=T_MGR_FUND_STAT, conn=conn, where=where
    )
    df[C_M_S] = df[C_M_S].apply(time_tool.format_date_str)
    df[C_M_E] = df[C_M_E].apply(
        lambda x: None if x is None else time_tool.format_date_str(x)
    )

    hammer.slim(df, show_index=1, a_f=1, name='基金经理任职基金统计表')
    return df


def get_mgr_fund_from_cache():
    global MGR_FUND_DF
    if MGR_FUND_DF is None:
        MGR_FUND_DF = get_mgr_fund_stat()
        del MGR_FUND_DF[C_F_T_2]
        MGR_FUND_DF = MGR_FUND_DF.rename(columns={
            C_F_T_1: 'fund_type', C_M_S: 'm_start', C_M_E: 'm_end'
        })
        MGR_FUND_DF = MGR_FUND_DF.fillna('BB')
        MGR_FUND_DF = MGR_FUND_DF[MGR_FUND_DF['fund_type'] != 'BB']
    hammer.slim(MGR_FUND_DF, name='基金经理历史管理基金对应关系表', a_f=1)
    return MGR_FUND_DF


@automatic_date_formatter_wrapper({
    'tradedate': {'fmt': quant_researcher.quant.project_tool.common_var.PY_DATE_FMT_WITH_N_DASH, 'class': 'normal_date'}})
def get_stock_style_by_code(x=None, date_related_filter=None, **kwargs):
    order_by = kwargs.pop('order_by', None)
    conn = kwargs.pop('conn', None)
    close_conn = False
    if conn is None:
        conn = db_conn.get_tk_factors_conn()
        close_conn = True
    select = ['tradedate', 'stockcode', '`style`', '`size_label`']
    where = []
    if x is not None:
        where.append(f'stockcode=\'{x}\'')
    if date_related_filter is not None:
        where.append(date_related_filter)
    df = my_mysql.read_v2(
        select=select, where=where, sfrom='stock_style', order_by=order_by,
        conn=conn
    )
    if close_conn:
        conn.close()
    return df


def get_stock_mkv(begin, end, stock_pool, con, mkv_type='流通市值'):
    type_dict = {'流通市值': 'freefloat_mktval', '总市值': 'total_mktval'}
    print('开始获取股票市值数据')
    mkv_df = pd.read_sql("select trade_date as tradedate, sec_code as code,"
                         f"{type_dict[mkv_type]} "
                         f"from astk_fn_daily_valuation "
                         f"where trade_date >= '{begin.replace('-', '')}'"
                         f"and trade_date <= '{end.replace('-', '')}'"
                         f"and sec_code in {tuple(stock_pool)}", con)
    mkv_df = mkv_df.set_index(['tradedate', 'code'])[type_dict[mkv_type]].unstack()
    mkv_df.index = pd.to_datetime(mkv_df.index)
    return mkv_df